<?php
/**********************************************************************************
 
	Updates the mySQL database from WoW addons' LUA databases
 
 	Copyright (C) 2010 Benjamin Thomas
 
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 	Author: Benjamin Thomas
	Email: thomas.ben@gmail.com
	Created: 05.21.10
 	Revised: 07.10.10

 	Calls: None.
 
 **********************************************************************************
 */
 
	require(dirname(__FILE__) . "/../config.php");
	
	$WTF = Vars::$wowfolder;
	$debug = Vars::$dbupdates;
	$logfolder = Vars::$logfolder;
  
/*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*
*			EDITING ANYTHING BELOW IS NOT ADVISED
*
*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/  
  
include(dirname(__FILE__) . '/../inc/dbcheck.php');
if (array_key_exists("confirm", $_GET)) { $confirm = true; } else { $confirm = false; }

$now = time();
$aborted = 1;

  
// Include the parser class
require_once(dirname(__FILE__) . '/../lib/MarketDB.php');
require_once(dirname(__FILE__) . '/../lib/WLP/Db/Mysql.php');
require_once(dirname(__FILE__) . '/../lib/WLP/Auctioneer/Scanner.php');
require_once(dirname(__FILE__) . '/../lib/Parser.php');
$db = new MarketDB();

if ($db->lastUpdate() == 1 && $confirm == false) {
	ob_start();
	printWarning();
    ob_end_flush(); ob_flush(); flush(); ob_start();
}


if ($debug) {
	if(!is_dir(dirname(__FILE__) . "$logfolder")) {
		mkdir(dirname(__FILE__) . "$logfolder");
	}
	$logfile = dirname(__FILE__) . "$logfolder$now.log";
	echo "This file will be logged to $logfile.<BR>You will not see any output until the script is complete.<BR><BR>";
	ob_flush();
	ob_start();
	echo "The current timestamp is (" . time() . ")<br />";
	echo "Last update was (" . $db->lastUpdate() . ")<br />";
}
register_shutdown_function('cleanup');

parseBeanCounter($WTF . 'BeanCounter.lua');
parseSkillet($WTF . 'Skillet.lua');
parseAuctioneer($WTF . 'Auc-Db.lua');
parseScans();
$aborted = 0;
$db->publicQuery("UPDATE db SET val = " . time() . " WHERE name = 'updated'");

function cleanup() {
	if (!$GLOBALS['aborted']) {
		echo "Database updated succesfully!<BR>";
	} else { 
		echo "There was an error updating the database and the script exited.<BR>";
	}
	if (Vars::$dbupdates) {
		echo "Writing to " . $GLOBALS['logfile'] . " ...";
		$fh = fopen($GLOBALS['logfile'], 'w') or die("Couldn't open log file for writing.");
		$out = ob_get_contents();
		$out = str_replace(array("<BR>", "<BR />","<br>","<br />"), "\r\n", $out);
		fwrite($fh, $out);
		fclose($fh);
		ob_end_flush();
	}
}


function parseSkillet($file) {
	print "Scanning Skillet ($file)...<br />";
	//ob_flush();
	
	//#############################
	//
	//	Parse Skillet LUA
	//
	//#############################
	// Instance a new WLP_Parser object and tell it what
	// Lua file to parse
	$wlpParser = new WLP_Parser($file);
	$luaArray = $wlpParser->toArray();
	$craft = $luaArray['SkilletDB']['servers']; //['Nagrand']['recipes'];
	$profName = array('Alchemy'=>1,'Blacksmithing'=>2,'Enchanting'=>4,'Engineering'=>5,'Inscription'=>7,'Jewelcrafting'=>8,'Leatherworking'=>9,'Mining'=>10,'Tailoring'=>11);
	$marketDB = new MarketDB();
	
	//Loop through each server
	foreach ($craft as $servername => &$server) {
		//Loop through each toon
		foreach($server['recipes'] as $toonname => &$toon) {
			//Get serverID
			$servername = mysql_real_escape_string($servername);
			$toonname = mysql_real_escape_string($toonname);
			$sql = "SELECT chars.charID FROM chars LEFT JOIN realms ON chars.server = realms.id";
			$sql .= " WHERE realms.name LIKE  '$servername%' AND chars.name =  '$toonname'";
			$dbcheck = $marketDB->publicQuery($sql);
			if ($dbcheck) {
				$dbcheck = $dbcheck[0];
				//Loop through each profession for the toon
				while (list($prof, $recipeList) = each($toon)) {
					//Ignore professions not a key in $profName
					if (array_key_exists($prof, $profName)) { 
						foreach ($recipeList as &$recipe) {
							//Reset the script's time limit for the current row
							set_time_limit(5);
							//ob_flush();
							//Skillet creates empty rows with values of 'nil'; ignoring them.
							if ($recipe != 'nil') {
								$arr = explode(";",$recipe);
								list(,$spellid,$title) = explode("|", $arr[1]);
								if ($arr[0]) {
								//Recipe has a name different than it's output
									$spell = mysql_real_escape_string($arr[0]);
									$where = "spellname = '$spell'";
								} else if ($spellid < 0) {
								//Skillet uses negative ids for spell ids
									$where = "spellid = " . abs($spellid);
								} else {
									$title = mysql_real_escape_string($title);
									$where = "spellname = '$title'";								
								}
								$sql = "SELECT craftID FROM crafting WHERE $where";
								$check = $marketDB->publicQuery($sql);
								if ($check) {
									$craftid = $check[0]['craftID'];
									$charid = $dbcheck['charID'];
									$sql = "SELECT * FROM craftingskills WHERE charid = $charid AND craftid = $craftid";
									$doublecheck = $marketDB->publicQuery($sql);
									if (!$doublecheck) {
										$sql = "INSERT INTO craftingskills (charid,craftid) VALUES($charid,$craftid)";
										$marketDB->publicQuery($sql);
									}									
								}
								if (!Vars::$dbupdates) {
									ob_flush(); flush();
								}
							}
						}
					}
					
				}
			}
		}
	}
}

//#############################
//
//	Parse BeanCounter LUA
//
//#############################
function parseBeanCounter($file) {
	// Instance a new WLP_Parser object and tell it what
	// Lua file to parse
	$wlpParser = new WLP_Parser($file);
	$luaArray = $wlpParser->toArray();
	$items = $luaArray["BeanCounterDB"]["ItemIDArray"];
	$servers = array();
	while (list($key,$val) = each($luaArray["BeanCounterDB"])) {
		if ($key != 'ItemIDArray' && $key != 'settings') {
			$servers[$key] = $val;
		}
	}
	$db = new MarketDB();
	foreach($servers as $server => &$chars) {
		foreach ($chars as $char => &$rows) {
			if (!Vars::$dbupdates) {
				ob_flush(); flush();
			}
			//Ignore all characters with invalid/unknown factions
			if ($rows['faction'] == "Alliance" || $rows['faction'] == "Horde") {
				echo "Accessing $char.<BR>";
				echo "Setting server: " . $server . " - " . $rows['faction'];
				$serverid = $db->setServer($server . " - " . $rows['faction']);
				echo  '{' , $serverid , '}<BR>';
				$charid = $db->getChar($char, $serverid);
				echo "charid: $charid<br />";
				$db->updateChar($charid, $rows['wealth']);
				//Search for all completed auctions
				foreach($rows['completedAuctions'] as $itemid => $data) {
					//Beancounter stores auctions inside keys we have no use for. Uses the 
					$data = array_reverse(current($data));
					foreach($data as &$salesstring) {
						list($stack, $money, $deposit , $fee, $buyout, $bid, $buyer, $time, , ) = explode(";", $salesstring);
						$bid = intval($bid);
						//echo "stack: $stack; money: $money; deposit: $deposit; fee: $fee; buyout: $buyout; bid: $bid; buyer: $buyer; time: $time;<BR>";
						// If the completed auction doesn't exist, create it
						if (!$db->checkCompletedAuction($itemid, $time, $charid)) {
							$db->createCompletedAuction($stack, $money, $deposit, $fee, $buyout, $bid, $buyer, $time, 0, $charid, $itemid);
						}					
					}
				}
				//Search for all purchases
				foreach($rows['completedBidsBuyouts'] as $itemid => $data) {
					foreach($data as &$uselessitemstrings) {
						//BeanCounter creates a bunch of random item seeds as keys because it hates me
						foreach($uselessitemstrings as $purchasestring) {
							$p = array();
							list($p['stackCount'], , , , $p['price'], , $p['seller'], $time, , ) = explode(";", $purchasestring);
							$db->checkBuyout($itemid, $time, $charid, $p);					
						}
					}
				}
				
			}
		}
	}

	while(list($key,$val) = each($items)) {
		if ( !$db->checkItem( current(explode(":", $key)) ) ) {
			$db->getItem( current(explode(":", $key)) );
		}
	}
}
	
function parseAuctioneer($file) {
	// Instance a new WLP_Db_Mysql object and give it the
	// needed credentials for connecting
	$wlpDb = new WLP_Db_Mysql(array(
		'hostname' => 'localhost',
		'username' => Vars::$dbuser,
		'password' => Vars::$dbpwd,
		'database' => Vars::$database
	));
	 
	print "Starting scan of $file...";
	//ob_flush();
	 // Instance a new WLP_Auctioneer_Scanner object and it
	// what Auc-Db.lua file it should parse, and pass in the
	// WLP_Db object
	$wlpAuc = new WLP_Auctioneer_Scanner($file, $wlpDb, true);
	print " complete.<BR>";
	print "Parsing Data.";
	parseScans();	
}

function parseScans() {
	$db = $GLOBALS['db'];
	$moving = 0.15;

	$sql = "DELETE FROM scans WHERE buyoutprice = 0";
	$db->publicQuery($sql);

	$last = $db->lastUpdate();
	//Uncomment next line to fully populate the database
	//$last = 1;
	$sql = "SELECT count(scanid) as seen, round(min(buyoutprice/stacksize)) as min, round(max(buyoutprice/stacksize)) as max,";
	$sql .= " round(stddev_pop(buyoutprice/stacksize)) as stddev, sum(stacksize) as total, sum(buyoutprice) as sumprice,";
	$sql .= " FROM_UNIXTIME(timestamp, '%Y/%m/%d') as day, itemid FROM scans WHERE FROM_UNIXTIME($last, '%Y/%m/%d')";
	$sql .= " <= FROM_UNIXTIME(timestamp, '%Y/%m/%d') GROUP BY day, itemid";
	echo $sql . "<br />";
	//exit();
	$result = $db->publicQuery($sql);

	foreach($result as $row) {
		set_time_limit(20);
		$id = $row['itemid'];
		if ($row['total'] == 0) {
			$total = 1;
		} else {
			$total = $row['total'];
		}			
		$avgprice = $row['sumprice'] / $total;
		$avgpost = $row['total'] / $row['seen'];
		$max = round($moving * $row['total'] / $avgpost); 
		if ($max == 0) { $max = 1; }
		$sql = "SELECT round(sum(b.price)/$max) as moving FROM (select buyoutprice/stacksize as price from scans where itemid = $id";
		$sql .= " and FROM_UNIXTIME(timestamp, '%Y/%m/%d') = '" . $row['day'] . "' AND buyoutprice > 0 ORDER BY price ASC";
		$sql .= " LIMIT 0, $max) as b WHERE 1 LIMIT 0,1;";
		$move = $db->publicQuery($sql);
		$sql = "SELECT date FROM scansummary WHERE date = '" . $row['day'] . "' AND itemid = $id";
		$check = $db->publicQuery($sql);
		if ($check) {
			$sql = "UPDATE scansummary SET seen = " . $row['seen'] . ", total = " . $row['total'] . ", moving = " . $move[0]['moving'];
			$sql .= ", min = {$row['min']}, max = {$row['max']}, avg = {$avgprice}, stddev = {$row['stddev']} WHERE date";
			$sql .= " = '" . $row['day'] . "' AND itemid = $id";
		} else {
			$sql = "INSERT INTO scansummary (seen, total, moving,min,max,avg,stddev,date,itemid)";
			$sql .= " VALUES ({$row['seen']},{$row['total']},{$move[0]['moving']},{$row['min']},{$row['max']},{$avgprice},{$row['stddev']},";
			$sql .= "'{$row['day']}',$id)";
		}
		$db->publicQuery($sql);
		ob_flush(); flush();
	}
	
	//Delete raw scan data older than 2 days
	$sql = "DELETE FROM scans WHERE FROM_UNIXTIME(timestamp, '%Y/%m/%d') < Date_sub(curdate(), INTERVAL 2 day)";
	$db->publicQuery($sql);	
	$db->publicQuery("OPTIMIZE TABLE  `scans`");
}

function skilletItemToName($str) {
	$arr = explode("|", $str);
	return $arr[2];
}

function skilletItemToColor($str) {
	$arr = explode("|", $str);
	echo "<BR>running skilletItemToColor(" . $str . ") = " . $arr[0] . ";<BR>";
	return $arr[0];
}

function skilletItemToID($str) {
	$arr = explode("|", $str);
	if ($arr[1] > 0) {
		return $arr[1];
	} else {
		return 0;
	}
}

function itemColorToQuality($str) {
	switch ($str) {
		case "9d9d9d": return 0; break;
		case "ffffff": return 1; break;
		case "1eff00": return 2; break;
		case "0070dd": return 3; break;
		case "a335ee": return 4; break;
		case "ff8000": return 5; break;
	}
}

function printWarning() {
	echo '<div style="width: 600px; text-align:center; margin:5px; color:#333333; padding:3px; border:#00FF00 solid 1px; background-color:#66FF66; background-image: url(\'images/info_bg.png\');">
			This is your first time updating your database, it will take a <strong>very long</strong> time to process all your data.<br />
			This script will be accessing data from various websites numerous times. 
			To prevent those servers from temporarily  banning your ip, the script will rest for a short period after each download.<br /><br />
			If this script times out while fetching data from online sources, refresh after a short break.
		</div><br /><br /><a href="updateDatabase.php?confirm=true">Click here to begin your scan</a>';
	ob_flush();
	exit();
}

?>